package dao.search;

import bean.search.RoomSearch;
import util.C3p0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 房间查询数据交互实现类
 * */
public class RoomSearchDao {
    public List<RoomSearch> roomSearch(int rNumber){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<RoomSearch> rs1 = null;

        String sql = "select r_number,r_sex,r_innumber,r_money,s_number,s_name,s_sex,s_c_number,s_tel from t_room\n" +
                "left join t_student on t_room.r_number = t_student.s_r_number where r_number = ?;";

        try {
            conn = C3p0Util.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,rNumber);
            rs = ps.executeQuery();
            while(rs.next()) {
                if (rs1 == null) {
                    rs1 = new ArrayList<RoomSearch>();
                }
                RoomSearch roomSearch = new RoomSearch();
                roomSearch.setrNumber(rs.getInt("r_number"));
                roomSearch.setrSex(rs.getString("r_sex"));
                roomSearch.setrInNumber(rs.getInt("r_innumber"));
                roomSearch.setrMoney(rs.getInt("r_money"));
                roomSearch.setsNumber(rs.getInt("s_number"));
                roomSearch.setsName(rs.getString("s_name"));
                roomSearch.setsSex(rs.getString("s_sex"));
                roomSearch.setScNumber(rs.getInt("s_c_number"));
                roomSearch.setsTel(rs.getInt("s_tel"));
                rs1.add(roomSearch);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return rs1;
    }
}
